<?php

class Members{

	public static function getMemberList(){
		$sql="SELECT * FROM ".MEMBERS;
		return executeReadQuery($sql);
	}
	
	public static function getMemberDetails($mem_id){
		$sql="SELECT * FROM ".MEMBERS." WHERE id={$mem_id}";
		return isset(executeReadQuery($sql)[0]) ? executeReadQuery($sql)[0] : NULL;
	}
	
	public static function getDueAmmount($memId,$month_year){
		$month_year=validateDate($month_year) ? formatDate($month_year,"Ym") : $month_year;
		$sql="SELECT due_left AS due FROM ".PAYMENT_RECIEVED." WHERE EXTRACT(YEAR_MONTH FROM applicable_from)='{$month_year}' AND mem_id={$memId}";
		$result=executeReadQuery($sql);
		return (isset($result[0]["due"])) ? (float)$result[0]["due"] : 0;
	}
	
	public static function getMaintenanceAmount($mem_id){
		$sql="SELECT (area*rate-concession) as amt from ".MEMBERS." WHERE id={$mem_id}";
		$result=executeReadQuery($sql);
		return isset($result[0]["amt"]) ? (float)$result[0]["amt"] : 0 ;
	}
	
	public static function getExtraCollectionDetails($month_year){
		$month_year=validateDate($month_year) ? formatDate($month_year,"Ym") : $month_year;
		$sql="SELECT * FROM ".EXTRA_COLLECTION." WHERE month_year='{$month_year}' ";
		$result=executeReadQuery($sql);
		return $result;
	
	}
	
	public static function getExtraCollectionAmount($month_year){
		$result=Members::getExtraCollectionDetails($month_year);
		$arr=[
			"amt"=>isset($result[0]["amt"]) ? (float)$result[0]["amt"] : 0 ,
			"reason"=>isset($result[0]["reason"]) ? $result[0]["reason"] : ""
		];
		return $arr ;
	}
	
	public static function getPaymentDetails($memId,$month_year){
		$month_year=validateDate($month_year) ? formatDate($month_year,"Ym") : $month_year;
		$sql="SELECT * FROM ".PAYMENT_RECIEVED." WHERE EXTRACT(YEAR_MONTH FROM applicable_from)='{$month_year}' AND mem_id={$memId}";
		$result=executeReadQuery($sql);
		return (isset($result[0])) ? $result[0] : NULL;
	
	}
	
	public static function getPayedAmount($memId,$month_year){
		$month_year=validateDate($month_year) ? formatDate($month_year,"Ym") : $month_year;
		$sql="SELECT total_amt_payed AS payedAmt FROM ".PAYMENT_RECIEVED." WHERE EXTRACT(YEAR_MONTH FROM applicable_from)='{$month_year}' AND mem_id={$memId}";
		$result=executeReadQuery($sql);
		return (isset($result[0]["payedAmt"])) ? (float)$result[0]["payedAmt"] : 0;
	}
	
	public static function calculateAmountPayable($memId,$date){
		$month_year=validateDate($date) ? formatDate($date,"Ym") : date("Ym");
		$maintenance=Members::getMaintenanceAmount($memId);
		$extra=Members::getExtraCollectionAmount($month_year);
		$payedAmount=Members::getPayedAmount($memId,$date);
		$date = new DateTime($month_year);
		$date->sub(new DateInterval('P1M'));
		$date= $date->format('Ym');
		$lastMonthDue=Members::getDueAmmount($memId,$date);
		$totlaAmmountDue=$maintenance+$extra["amt"]+$lastMonthDue-$payedAmount;
		return [
			"maintenanceAmt"=>$maintenance,
			"lastMonthDue"=>$lastMonthDue,
			"extraAmt"=>$extra["amt"],
			"extraAmtFor"=>$extra["reason"],
			"total_amt_payable"=>$totlaAmmountDue,
			"totalAmtPayed"=>$payedAmount,
		];
		
	}
}
?>